package com.superman.dao.impl;

import com.superman.dao.IRecordDao;
import com.superman.entity.Record;
import com.superman.entity.Record_User_Car;
import com.superman.util.JDBCTemplate;
import com.superman.util.ResultSetHandler;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class RecordDaoImpl implements IRecordDao {
    /**
     * 根据传入的sql语句和参数，返回符合指定要求的租车记录集合
     * @param sql 要求执行的语句
     * @param params 语句中的参数
     * @return 符合要求的租车记录集合
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    private List<Record_User_Car> queryRecord(String sql, Object[] params) throws SQLException, ClassNotFoundException {
        return JDBCTemplate.query(sql, params, new ResultSetHandler<Record_User_Car>(){
            @Override
            public Record_User_Car handleRow(ResultSet rs) throws SQLException {

                String id = rs.getString("id");
                String user_id = rs.getString("user_id");
                String car_id = rs.getString("car_id");

                return new Record_User_Car(
                        rs.getString("id"), rs.getString("user_id"), rs.getString("car_id"),
                        rs.getString("start_date"), rs.getString("return_date"), rs.getDouble("payment"),
                        rs.getString("username"), rs.getString("password"), rs.getInt("sex"),
                        rs.getString("id_number"), rs.getString("tel"), rs.getString("addr"),
                        rs.getInt("type"),
                        rs.getString("car_number"), rs.getString("brand_id"), rs.getString("model"),
                        rs.getString("color"), rs.getString("category_id"), rs.getString("t_comments"),
                        rs.getDouble("price"), rs.getDouble("rent"),
                        rs.getInt("status"), rs.getInt("useable"),
                        rs.getString("brand"), rs.getString("category")
                );
            }
        });
    }

    /**
     * 返回当前数据库中的所有租车记录
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Override
    public List<Record_User_Car> queryRecordAll() throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_record_user_car_view";
        Object[] params = null;
        return queryRecord(sql, params);
    }

    /**
     * 返回指定用户的租车记录
     * @param user_id 指定用户的id
     * @return 该用户的租车记录
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Override
    public List<Record_User_Car> queryRecordByUserID(String user_id) throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_record_user_car_view WHERE user_id = ?";
        Object[] params = {user_id};
        return queryRecord(sql, params);
    }

    /**
     * 返回指定用户尚未归还的租车记录
     * @param user_id 指定用户的id
     * @return 该用户尚未归还的租车记录
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Override
    public List<Record_User_Car> queryRecordByUserIdAndNoReturnInfo(String user_id) throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_record_user_car_view WHERE user_id = ? AND return_date is null";
        Object[] params = {user_id};
        return queryRecord(sql, params);
    }

    /**
     * 返回指定汽车的租车记录
     * @param car_id 指定汽车的id
     * @return 该汽车的租车记录
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Override
    public List<Record_User_Car> queryRecordByCarID(String car_id) throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_record_user_car_view WHERE car_id = ?";
        Object[] params = {car_id};
        return queryRecord(sql, params);
    }

    /**
     * 返回指定汽车（当前被租用）的租车记录
     * @param car_id 指定汽车的id
     * @return 该汽车当前被租用记录
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Override
    public List<Record_User_Car> queryRecordByCarIdAndNoReturnInfo(String car_id) throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_record_user_car_view WHERE car_id = ? AND status = 1";
        Object[] params = {car_id};
        return queryRecord(sql, params);
    }


    /**
     * 返回指定的租车记录
     * @param record_id
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Override
    public List<Record_User_Car> queryRecordByRecordID(String record_id) throws SQLException, ClassNotFoundException {
        String sql = "SELECT * FROM t_record_user_car_view WHERE id = ?";
        Object[] params = {record_id};
        return queryRecord(sql, params);
    }

    /**
     * 当发生车辆租用时，租车记录表中插入一条租车记录
     * @param record 要插入的租车记录
     * @param conn 车辆租用操作的连接
     * @return 成功插入的记录行数
     * @throws SQLException
     */
    @Override
    public int insertRecord(Record record, Connection conn) throws SQLException {
        String sql = "INSERT INTO T_RECORD VALUES (?, ?, ?, to_date(? ,'yyyy-mm-dd hh24:mi:ss'), ?, ?)";
        Object[] params = {
                record.getId(), record.getUser_id(), record.getCar_id(), record.getStart_date(), record.getReturn_date(), record.getPayment()
        };
        return JDBCTemplate.update(sql, params, conn);
    }

    /**
     * 当车辆归还时，完善租车记录表中相应的租车记录
     * @param car_id 租用的车辆id
     * @param return_date 车辆归还时间
     * @param payment 租金总额
     * @param conn 当前连接
     * @return 成功更新的行数
     * @throws SQLException
     */
    @Override
    public int updateRecord(String car_id, String return_date, double payment,Connection conn) throws SQLException {
        String sql = "UPDATE t_record SET return_date = to_date(? ,'yyyy-mm-dd hh24:mi:ss'), payment = ? WHERE car_id = ? AND return_date IS NULL";
        Object[] params = {return_date, payment, car_id};
        return JDBCTemplate.update(sql, params, conn);
    }
}
